<?
class perito{

	// Propiedades
	var $id;
	var $codigo_asociacion;
	var $letra;
	var $cedula;
	var $nombre;
	var $id_zona;
	var $nombre_zona;
	var $limites;
	var $estatus;
	var $fecha_reintegro;
	var $observaciones;
	var $historico;
	var $tasa_asociacion;
	var $tasa_fideicomiso;
	var $fecha_nacimiento;
	var $telefonos;
	var $correo_electronico;
	var $direccion;
	var $convenios;

	var $total;

	function get($conn, $id){
		try {
			$q = "SELECT A.*, B.nombre AS nombre_zona FROM peritos AS A INNER JOIN zonas AS B ON A.id_zona = B.id WHERE A.id=COALESCE(NULLIF('$id',''),'0')::integer";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			if(!$r->EOF){
				$this->id = $r->fields['id'];
				$this->codigo_asociacion = $r->fields['codigo'];
				$this->letra= $r->fields['letra'];
				$this->cedula = $r->fields['cedula'];
				$this->nombre = $r->fields['nombre'];
				$this->id_zona = $r->fields['id_zona'];
				$this->nombre_zona = $r->fields['nombre_zona'];
				$this->estatus = $r->fields['estatus'];
				$this->fecha_reintegro = $r->fields['fecha_reintegro'];
				$this->fecha_nacimiento = $r->fields['fecha_nacimiento'];
				$this->telefonos = $r->fields['telefonos'];
				$this->correo_electronico= $r->fields['correo_electronico'];
				$this->observaciones = $r->fields['observaciones'];
				$this->tasa_asociacion = $r->fields['tasa_asociacion'];
				$this->tasa_fideicomiso = $r->fields['tasa_fideicomiso'];
				$this->direccion = $r->fields['direccion'];

				$q = "SELECT A.id_tipo AS id, B.nombre AS nombre, SUM(A.cantidad) AS cantidad 
					FROM limites_peritos AS A 
						INNER JOIN tipos_experticia AS B ON A.id_tipo = B.id
					WHERE id_perito = $this->id
					GROUP BY 1,2";
				//die($q);
				$rL = $conn->Execute(utf8_encode($q));
				$i = 0;
				while(!$rL->EOF){
					$LimitesAux[$i][0] = $rL->fields['id'];
					$LimitesAux[$i][1] = $rL->fields['nombre'];
					$LimitesAux[$i][2] = $rL->fields['cantidad'];

					$i++;

					$rL->movenext();
				}
				$this->limites = new Services_JSON();
				$this->limites = (is_array($LimitesAux) ? $this->limites->encode($LimitesAux) : false);

				$q = "SELECT A.id_perito_convenio AS id, B.nombre AS nombre, SUM(A.monto) AS monto 
					FROM convenios_peritos AS A 
						INNER JOIN peritos AS B ON A.id_perito_convenio = B.id
					WHERE A.id_perito_jubilado = $this->id
					GROUP BY 1,2";
				//die($q);
				$rC = $conn->Execute(utf8_encode($q));
				$i = 0;
				while(!$rC->EOF){
					$ConveniosAux[$i][0] = $rL->fields['id'];
					$ConveniosAux[$i][1] = $rL->fields['nombre'];
					$ConveniosAux[$i][2] = $rL->fields['monto'];

					$i++;

					$rC->movenext();
				}
				$this->convenios = new Services_JSON();
				$this->convenios = (is_array($ConveniosAux) ? $this->convenios->encode($ConveniosAux) : false);
				
				$q="SELECT * FROM hist_estatus_perito WHERE id_perito = $this->id ORDER BY fecha DESC, id DESC";
				$rH = $conn->Execute(utf8_encode($q));
				$i = 0;
				while(!$rH->EOF) {
					$HistorialAux[$i][0] = $rH->fields['fecha'];
					switch ($rH->fields['estatus']){
						case "A":
							$HistorialAux[$i][1] = "ACTIVO";
							break;
						case "S":
							$HistorialAux[$i][1] = "SUSPENDIDO";
							break;
						case "E":
							$HistorialAux[$i][1] = "EXPULSADO";
							break;
						case "J":
							$HistorialAux[$i][1] = "JUBILADO";
							break;
						case "D":
							$HistorialAux[$i][1] = "DIFUNTO";
							break;
					}

					$HistorialAux[$i][2] = $rH->fields['observaciones'];
					$HistorialAux[$i][3] = $rH->fields['reintegro'];

					$i++;

					$rH->movenext();
				}

				$this->historico = $HistorialAux;				


			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function get_all($conn, $orden="nombre", $Tipo, $Valor, $TipoE, $max=10, $from=1,$id_zona){
		try {
			$TipoE = empty($TipoE) ? 0 : $TipoE;
			
			if(empty($Valor)){
				$q = "SELECT id FROM peritos WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona)  ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==0){
				$q = "SELECT id FROM peritos WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona)  ";
				$q.= "AND id::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			//	die($q);			

			}
			elseif($Tipo==1){
				$q = "SELECT id FROM peritos WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona)  ";
				$q.= "AND codigo::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}

			elseif($Tipo==2){
				$q = "SELECT id FROM peritos WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona)  ";
				$q.= "AND cedula::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==3){
				$q = "SELECT id FROM peritos WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona)  ";
				$q.= "AND nombre::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}

			//die($q);			
			$r = ($max!=0) ? $conn->SelectLimit($q, $max, $from) : $conn->Execute(utf8_encode($q));
			$coleccion=array();
			while(!$r->EOF){
				$ue = new perito;
				$ue->get($conn, $r->fields['id']);
				$coleccion[] = $ue;
				$r->movenext();
			}
			$this->total = $r->RecordCount();
			return $coleccion;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function add($conn, $codigo, $cedula, $nombre, $id_zona, $limites, $estatus, $fecha_reintegro, $observaciones, $tasa_asociacion, $tasa_fideicomiso, $fecha_nacimiento, $telefonos, $correo_electronico, $letra, $direccion, $convenios){
		try {
			$q = "INSERT INTO peritos ";
			$q.= "(codigo, cedula, nombre, id_zona, estatus, fecha_reintegro, observaciones, tasa_asociacion, tasa_fideicomiso, fecha_nacimiento, telefonos, correo_electronico, letra, direccion) ";
			$q.= "VALUES ";
			$q.= "(upper('$codigo'), upper('$cedula'), upper('$nombre'), $id_zona, '$estatus', NULLIF('$fecha_reintegro','')::date, upper('$observaciones'), $tasa_asociacion, $tasa_fideicomiso, NULLIF('$fecha_nacimiento','')::date, upper('$telefonos'), lower('$correo_electronico'), upper('$letra'), upper('$direccion')) ";

			//$q.= " RETURNING id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));

			$this->auditoria($conn, $q);
			
			$q2="SELECT MAX(id) AS id FROM peritos";
			$r2 = $conn->Execute(utf8_encode($q2));
			
			//$this->GuardarLimites($conn, $r->fields['id'], $limites);
			$this->GuardarLimites($conn, $r2->fields['id'], $limites);
			$this->GuardarConvenios($conn, $r2->fields['id'], $convenios);
			
			return REG_ADD_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function set($conn, $id, $codigo, $cedula, $nombre, $id_zona, $limites, $estatus, $fecha_reintegro, $observaciones, $tasa_asociacion, $tasa_fideicomiso, $fecha_nacimiento, $telefonos, $correo_electronico, $letra, $direccion, $convenios){
		try {

			$q = "UPDATE peritos SET codigo=upper('$codigo'), cedula=upper('$cedula'), nombre=upper('$nombre'), id_zona =$id_zona, estatus = '$estatus', fecha_reintegro = NULLIF('$fecha_reintegro','')::date, observaciones = upper('$observaciones'), tasa_asociacion = $tasa_asociacion, tasa_fideicomiso = $tasa_fideicomiso, fecha_nacimiento = NULLIF('$fecha_nacimiento','')::date, telefonos = upper('$telefonos'), correo_electronico = lower('$correo_electronico'), letra = upper('$letra'), direccion = upper('$direccion') ";
			$q.= "WHERE id=$id";	
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);

			$this->GuardarLimites($conn, $id, $limites);
			
			$this->GuardarConvenios($conn, $id, $convenios);
			
			return REG_SET_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function del($conn, $id){
		try {
			$q = "DELETE FROM peritos WHERE id=$id";
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_DEL_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function GuardarLimites($conn,$id,$limites){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$limites));
			$q = "DELETE FROM limites_peritos WHERE id_perito=$id";
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			if(is_array($JsonRec->limites)){
				foreach($JsonRec->limites as $limitesAux){
					$cantidad = guardafloat($limitesAux[2]);
					$q = "INSERT INTO limites_peritos ";
					$q.= "(id_perito, id_tipo, cantidad) ";
					$q.= "VALUES ";
					$q.= "($id, $limitesAux[0], $cantidad) ";
					//die($q);
					$conn->Execute(utf8_encode($q));
					
					$this->auditoria($conn, $q);
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function GuardarConvenios($conn,$id,$convenios){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$convenios));
			$q = "DELETE FROM convenios_peritos WHERE id_perito_jubilado=$id";
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			if(is_array($JsonRec->convenios)){
				foreach($JsonRec->convenios as $conveniosAux){
					$monto = guardafloat($conveniosAux[2]);
					$q = "INSERT INTO convenios_peritos ";
					$q.= "(id_perito_jubilado,id_perito_convenio, monto) ";
					$q.= "VALUES ";
					$q.= "($id, $conveniosAux[0], $monto) ";
					//die($q);
					$conn->Execute(utf8_encode($q));
					
					$this->auditoria($conn, $q);
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function total_registro_busqueda($conn,$Tipo, $Valor, $TipoE, $orden="id", $id_zona)
	{
		$TipoE = empty($TipoE) ? 0 : $TipoE;
		if(empty($Valor)){
			$q = "SELECT count(*) AS total FROM peritos WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona) ";
		}
		elseif($Tipo==0){
			$q = "SELECT count(*) AS total FROM peritos  WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona) ";
			$q.= " AND id::text ILIKE '%$Valor%'::text";
		}
		elseif($Tipo==1){
			$q = "SELECT count(*) AS total FROM peritos  WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona) ";
			$q.= " AND codigo::text ILIKE '%$Valor%'::text";
		}
		elseif($Tipo==2){
			$q = "SELECT count(*) AS total FROM peritos  WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona) ";
			$q.= " AND cedula::text ILIKE '%$Valor%'::text";
		}

		elseif($Tipo==3){
			$q = "SELECT count(*) AS total FROM peritos  WHERE id_zona IN (SELECT id FROM zonas WHERE id = $id_zona OR id_padre =$id_zona) ";
			$q.= " AND nombre::text ILIKE '%$Valor%'::text";
		}

		$r = $conn->Execute(utf8_encode($q));
		$total = $r->fields['total'];
		return $total;
	}

	function auditoria($conn, $query) {
		$id_usuario = $_SESSION['id'];
		$q = "INSERT INTO auditoria(id_usuario, query) VALUES($id_usuario,$$" . $query . "$$);";
		//die($q);
		$conn->Execute(utf8_encode($q));

		return true;
	}	
	

}
?>
